USE [ANTS_NetServer]
GO

/****** Object:  StoredProcedure [dbo].[proc_paged_with_Midvar]    Script Date: 04/21/2013 15:27:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[proc_paged_with_Midvar]  --利用ID>最大ID值和中间变量 
 ( 
    @pageIndex int, 
     @pageSize int 
 ) 
 as 
     declare @count int 
     declare @ID int 
     declare @timediff datetime 
    declare @sql nvarchar(500) 
begin 
set nocount on; 
    select @count=0,@ID=0,@timediff=getdate() 
    select @count=@count+1,@ID=case when @count<=@pageSize*@pageIndex then ID else @ID end from tb_testTable order by id 
    set @sql='select top '+str(@pageSize)+' * from tb_testTable where ID>'+str(@ID) 
    execute(@sql) 
    select datediff(ms,@timediff,getdate()) as 耗时 
set nocount off; 
end

GO

